package com.jeeplus.test.tools.utils;

import com.jeeplus.sys.domain.DictValue;
import com.jeeplus.sys.service.DictValueService;
import com.jeeplus.sys.service.dto.DictValueDTO;
import com.jeeplus.sys.service.mapstruct.DictValueWrapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelHeaderGenerator {

    /**
     * 动态生成Excel表头并创建Excel文件（支持双层表头）
     *
     * @param headers   双层表头数据（第一层表头为键，第二层表头为值）
     * @param filePath  文件保存路径（包含文件名，例如：/path/to/example.xlsx）
     * @param sheetName 工作表名称
     */
    public static void generateExcelWithDoubleHeaders(List<List<String>> headers, String filePath, String sheetName) {
        // 创建一个新的工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet(sheetName);

        // 设置表头样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true); // 加粗
        headerFont.setFontHeightInPoints((short) 12); // 字体大小
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中

        // 填充表头
        for (int i = 0; i < headers.size(); i++) {
            Row headerRow = sheet.createRow(i);
            // 填充第二层表头
            for (int j = 0; j < headers.get(i).size(); j++) {
                Cell secondLevelCell = headerRow.createCell(j);
                secondLevelCell.setCellValue(headers.get(i).get(j));
                secondLevelCell.setCellStyle(headerCellStyle);
                sheet.autoSizeColumn(i);
            }
        }

        // 检查并创建目录
        File file = new File(filePath);
        File parentDir = file.getParentFile();
        if (parentDir != null && !parentDir.exists()) {
            parentDir.mkdirs(); // 创建父目录
        }

        // 写入文件
        try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
            workbook.write(fileOut);
            System.out.println("Excel文件已生成: " + filePath);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void newGenerateExcelWithDoubleHeaders(List<List<String>> headers, String filePath, String sheetName, DictValueService dictValueService, DictValueWrapper dictValueWrapper) {
        // 创建一个新的工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet(sheetName);

        // 设置表头样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true); // 加粗
        headerFont.setFontHeightInPoints((short) 12); // 字体大小
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中

        // 设置数据行样式
        CellStyle dataCellStyle = workbook.createCellStyle();
        Font dataFont = workbook.createFont();
        dataFont.setBold(false); // 不加粗
        dataFont.setFontHeightInPoints((short) 12); // 字体大小
        dataFont.setFontName("宋体"); // 设置字体为宋体
        dataCellStyle.setFont(dataFont);
        dataCellStyle.setAlignment(HorizontalAlignment.LEFT); // 左对齐

        // 填充表头
        for (int i = 0; i < headers.size() - 1; i++) {
            Row headerRow = sheet.createRow(i);
            // 填充第二层表头
            for (int j = 0; j < headers.get(i).size(); j++) {
                Cell secondLevelCell = headerRow.createCell(j);
                secondLevelCell.setCellValue(headers.get(i).get(j));
                secondLevelCell.setCellStyle(headerCellStyle); // 应用表头样式
                sheet.autoSizeColumn(j);
            }
        }

        // 固定列宽
        for (int j = 0; j < headers.get(0).size(); j++) {
            sheet.setColumnWidth(j, 20 * 256);
        }

        // 1. 材料类别添加为下拉选
        int materialCategoryColIdx = -1;
        for (List<String> headerRow : headers) {
            for (int colIdx = 0; colIdx < headerRow.size(); colIdx++) {
                String originalValue = headerRow.get(colIdx).trim();
                if (originalValue.replaceAll("\\s+", "").equalsIgnoreCase("材料类别")) {
                    materialCategoryColIdx = colIdx;
                    break;
                }
            }
            if (materialCategoryColIdx != -1) break; // 找到后提前退出
        }

        if (materialCategoryColIdx != -1) {
            String[] materialOptions = loadMaterialOptionsFromConfig(dictValueService,dictValueWrapper);

            for (int i = 0; i < materialOptions.length; i++) {
                // 按中文冒号分割，取后半部分
                materialOptions[i] = materialOptions[i].split("：")[1];
            }
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(materialOptions);

            CellRangeAddressList addressList = new CellRangeAddressList(
                    1,
                    1048575,
                    materialCategoryColIdx,
                    materialCategoryColIdx
            );

            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            validation.setSuppressDropDownArrow(true);
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.createErrorBox("输入错误", "请从下拉列表中选择有效选项");
            validation.setShowErrorBox(true); // 显示错误提示框
            sheet.addValidationData(validation);
        } else {
            System.err.println("警告：未找到'材料类别'列，请检查表头配置");
        }



        // 检查并创建目录
        File file = new File(filePath);
        File parentDir = file.getParentFile();
        if (parentDir != null && !parentDir.exists()) {
            parentDir.mkdirs();
        }

        // 写入文件
        try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
            workbook.write(fileOut);
            System.out.println("Excel文件已生成: " + filePath);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    private static String[] loadMaterialOptionsFromConfig(DictValueService dictValueService,DictValueWrapper dictValueWrapper) {
        String dictTypeId = "1898906530877710338";
        List<DictValueDTO> dictValues =  dictValueService.lambdaQuery ( ).eq ( DictValue::getDictTypeId, dictTypeId ).orderByAsc ( DictValue::getSort ).list ( ).stream ( ).map ( dictValueWrapper::toDTO ).collect ( Collectors.toList ( ) );

        return dictValues.stream()
                .map(dv -> dv.getValue() + "：" + dv.getLabel())
                .toArray(String[]::new);
    }



    public static void main(String[] args) {
        // 示例：动态生成双层表头
        List<List<String>> headers = new ArrayList<>(); // 使用 LinkedHashMap 保持顺序
        headers.add(Arrays.asList("ID", "NAME", "AGE", "SEX", "ADDRESS"));
        headers.add(Arrays.asList("ID", "姓名", "年龄", "性别", "地址"));

        String filePath = "./jeeplus-web/src/main/resources/static/111/中文名称2.xlsx"; // 自定义文件保存路径
        String sheetName = "用户信息";

        generateExcelWithDoubleHeaders(headers, filePath, sheetName);
    }
}

